import datetime
import http
import os
from datetime import date

import pandas
import xlrd
from xlrd import open_workbook
from fastapi import File, UploadFile
from Model import session, ExpiringContract, Base, engine
from Model.base_models import ContractCode
from Utils.router import router


@router.post("/gy/contract_add", tags=['合同'], summary='添加即将到期合同')
async def contract_re_add(args: ContractCode):
    """
    添加即将到期合同
    :return:
    """
    try:
        session.add(ExpiringContract(
            expiry_date=args.expiry_date,
            shop_name=args.shop_name,
            merchant_name=args.merchant_name,
            code_id=args.code_id,
            principal=args.principal
        ))
        session.commit()
        return {"code": http.HTTPStatus.OK, "msg": "添加数据成功", 'data': args, "return_code": 1}
    except:
        session.rollback()
        return {"code": http.HTTPStatus.OK, "msg": "添加数据失败", "return_code": 0}


@router.post("/gy/contract_add_file", tags=['合同'], summary='添加即将到期合同-导入模式')
async def file_upload(file: UploadFile = File(...)):
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
    excel_name = await file.read()
    bk = xlrd.open_workbook(file_contents=excel_name)

    try:
        sh = bk.sheets()[0]  # 因为Excel里只有sheet1有数据，如果都有可以使用注释掉的语句
        # sh = bk.sheet_by_name("benke.xlsx")
    except:
        print("no sheet in %s named sheet1" % excel_name)
    else:
        nrows = sh.nrows
        ncols = sh.ncols
        row_list = []
        for i in range(1, nrows):
            row_date = sh.row_values(i)
            n = i - 1
            sql_send = ExpiringContract()
            data_value = xlrd.xldate_as_tuple(row_date[14], bk.datemode)
            tmp = date(*data_value[:3]).strftime('%Y-%m-%d')
            sql_send.serial_number = row_date[0]
            sql_send.expiry_date = str(tmp)
            sql_send.shop_name = row_date[9]
            sql_send.code_id = row_date[3]
            session.add(sql_send)
        session.commit()
        session.close()
        return {"code": http.HTTPStatus.OK, "msg": "添加数据成功", "return_code": 1}


@router.get("/gy/contract_load", tags=['合同'], summary='查询即将到期合同')
def contract_re():
    """
    查询即将到期的合同
    """
    query = session.query(ExpiringContract).all()
    session.commit()
    data_info = []
    count = 0
    for i in query:
        count = count + 1
        now_Y = datetime.datetime.now().strftime("%Y")
        now_m = datetime.datetime.now().strftime("%m")
        now_d = datetime.datetime.now().strftime("%d")
        date_num = (datetime.date(int(i.expiry_date.strftime("%Y")), int(i.expiry_date.strftime("%m")),
                                  int(i.expiry_date.strftime("%d"))) - datetime.date(int(now_Y), int(now_m),
                                                                                     int(now_d))).days
        if date_num <= 20:
            info = {'shop_name': i.shop_name, 'expiry_date': i.expiry_date, 'code_id': i.code_id}
            data_info.append(info)
    return {'code': 0, 'count': count, 'data': data_info}
